﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MvcBlog.Models
{
    public class PostsRepository : IDisposable
    {
        // Query Expression Syntax
        // Method-Based Query Syntax
        BlogDbContext db = new BlogDbContext();

        /// <summary> Get selected post </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Post GetPost(int id)
        {
            return db.Posts.Include("Comments").Single(p => p.PostID == id);
        }

        /// <summary> Get all posts and show latest posts first </summary>
        /// <returns></returns>
        public List<Post> GetPosts()
        {
            var qry = from p in db.Posts
                      orderby p.PostID descending
                      select p;
            return qry.ToList();
        }

        public List<Post> GetPosts2()
        {
            var posts = db.Posts.OrderByDescending(p => p.PostID);
            return posts.ToList();
        }

        public List<Post> GetPosts(int category)
        {
            var qry = from p in db.Posts
                      where p.CategoryID == category
                      select p;

            return qry.ToList();
        }

        /// <summary> Get post by month </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public List<Post> GetPosts(int year, int month)
        {
            List<Post> posts = new List<Post>();
            var qry = from p in db.Posts
                      where (p.PostDate.Year == year && p.PostDate.Month == month)
                      orderby p.PostID descending
                      select p;
            posts = qry.ToList();
            return posts;
        }

        public List<Post> GetPosts(string searchString)
        {
            var posts = from p in db.Posts
                        where p.Title.Contains(searchString) || p.Content.Contains(searchString)
                        select p;
            return posts.ToList();
        }

        public List<Post> GetPosts2(string searchString)
        {
            var posts = db.Posts.Where(p => p.Title.Contains(searchString) || p.Content.Contains(searchString));
            return posts.ToList();
        }

        public List<Post> GetLatestPosts2()
        {
            const int count = 5;
            var posts = db.Posts
                          .OrderByDescending( p => p.PostDate)
                          .Take(count);

            return posts.ToList();
        }

        public List<Post> GetLatestPosts()
        {
            const int count = 5;
            var qry = (from p in db.Posts     
                      orderby p.PostDate descending
                      select p).Take(count);

            return qry.ToList();
        }

        /// <summary> Get Arhives List </summary>
        /// <returns></returns>
        public List<Arhives> GetArhives()
        {
            List<Arhives> arh = new List<Arhives>();
            var qry = from p in db.Posts
                      group p by new { p.PostDate.Month, p.PostDate.Year } into grp
                      select new { grp.Key.Month, grp.Key.Year };
            foreach (var item in qry)
            {
                Arhives a = new Arhives{
                    Month = item.Month,
                    Year = item.Year};

                arh.Add(a);
            }

            return arh;
        }

        public List<Arhives> GetArhives2()
        {
            List<Arhives> arh = new List<Arhives>();
            var qry = db.Posts.GroupBy( w => new { w.PostDate.Year, w.PostDate.Month},
                key1 => new {key1.PostDate.Year, key1.PostDate.Month}); 
                      
            foreach (var item in qry)
            {
                Arhives a = new Arhives
                {
                    Month = item.Key.Month,
                    Year = item.Key.Year
                };

                arh.Add(a);
            }

            return arh;
        }

        public List<PostCategory> GetPostCategories()
        {
            return db.PostCategories.ToList();
        }

        public void Dispose()
        {
            db.Dispose();
        }
    }
}